﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using DevExpress.XtraEditors;
using SmartFrame_DataCollect.Models;
using MongoDB.Driver;
using MongoDB.Bson;
using System.Linq.Expressions;

namespace SmartFrame_DataCollect
{
    public partial class DataConfigTab : BaseModule
    {
        public frmMain frmMain1 { get; set; }
        //数据库名称
        string msSQLDBName = DataBaseHelper.CJDBName;

        int pageIndex1 = 1;
        int totalPage1 = 0;

        int pageIndex2 = 1;
        int totalPage2 = 0;

        public DataConfigTab()
        {
            //frmMain1 = _frmMain;
            InitializeComponent();
            //初始化数据配置
            InitDataConfig();
        }
        public void ChangeDataConfig(string defaultDB)
        {
            switch (defaultDB)
            {
                case "MSSQL":
                    xtraTabControl1.SelectedTabPage = xtraTabControl1.TabPages[0]; break;
                case "MongoDB":
                    xtraTabControl1.SelectedTabPage = xtraTabControl1.TabPages[1]; break;
                case "Redis":
                    xtraTabControl1.SelectedTabPage = xtraTabControl1.TabPages[2]; break;
            }
        }


        private void InitDataConfig()
        {
            DataTable dbTable = DbHelperSQL.Query("select * from " + msSQLDBName + @".dbo.Ba_DBConfig where DBConfigType='mssql' ").Tables[0];
            if (dbTable.Rows.Count > 0)
            {
                EditMSSQLConnStr.EditValue = dbTable.Rows[0]["ConnStr"];
                ckEditSubTable.Checked = bool.Parse(dbTable.Rows[0]["IsSubTable"].ToString());
                cbEditSubTableType.EditValue = dbTable.Rows[0]["SubTableType"];
            }

            DataTable dbTable2 = DbHelperSQL.Query("select * from " + msSQLDBName + @".dbo.Ba_DBConfig where DBConfigType='redis' ").Tables[0];
            if (dbTable2.Rows.Count > 0)
            {
                EditRedisConnStr.EditValue = dbTable2.Rows[0]["ConnStr"];
                cboEditDB.SelectedItem = dbTable2.Rows[0]["DBNum"].ToString();
            }

            DataTable dbTable3 = DbHelperSQL.Query("select * from " + msSQLDBName + @".dbo.Ba_DBConfig where DBConfigType='mongodb' ").Tables[0];
            if (dbTable3.Rows.Count > 0)
            {
                EditMongoDBConnStr.EditValue = dbTable3.Rows[0]["ConnStr"];
            }


            //开始时间
            this.bgDateEdit1.Properties.VistaDisplayMode = DevExpress.Utils.DefaultBoolean.True;
            this.bgDateEdit1.Properties.VistaEditTime = DevExpress.Utils.DefaultBoolean.True;
            this.bgDateEdit1.Properties.DisplayFormat.FormatString = "yyyy-MM-dd HH:mm";
            this.bgDateEdit1.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.bgDateEdit1.Properties.EditFormat.FormatString = "yyyy-MM-dd HH:mm";
            this.bgDateEdit1.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.bgDateEdit1.Properties.Mask.EditMask = "yyyy-MM-dd HH:mm";
            //结束时间
            this.edDateEdit1.Properties.VistaDisplayMode = DevExpress.Utils.DefaultBoolean.True;
            this.edDateEdit1.Properties.VistaEditTime = DevExpress.Utils.DefaultBoolean.True;
            this.edDateEdit1.Properties.DisplayFormat.FormatString = "yyyy-MM-dd HH:mm";
            this.edDateEdit1.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.edDateEdit1.Properties.EditFormat.FormatString = "yyyy-MM-dd HH:mm";
            this.edDateEdit1.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.edDateEdit1.Properties.Mask.EditMask = "yyyy-MM-dd HH:mm";
            //开始时间
            this.bgDateEdit2.Properties.VistaDisplayMode = DevExpress.Utils.DefaultBoolean.True;
            this.bgDateEdit2.Properties.VistaEditTime = DevExpress.Utils.DefaultBoolean.True;
            this.bgDateEdit2.Properties.DisplayFormat.FormatString = "yyyy-MM-dd HH:mm";
            this.bgDateEdit2.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.bgDateEdit2.Properties.EditFormat.FormatString = "yyyy-MM-dd HH:mm";
            this.bgDateEdit2.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.bgDateEdit2.Properties.Mask.EditMask = "yyyy-MM-dd HH:mm";
            //结束时间
            this.edDateEdit2.Properties.VistaDisplayMode = DevExpress.Utils.DefaultBoolean.True;
            this.edDateEdit2.Properties.VistaEditTime = DevExpress.Utils.DefaultBoolean.True;
            this.edDateEdit2.Properties.DisplayFormat.FormatString = "yyyy-MM-dd HH:mm";
            this.edDateEdit2.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.edDateEdit2.Properties.EditFormat.FormatString = "yyyy-MM-dd HH:mm";
            this.edDateEdit2.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            this.edDateEdit2.Properties.Mask.EditMask = "yyyy-MM-dd HH:mm";




            //查询日期
            this.bgDateEdit3.Properties.DisplayFormat.FormatString = "yyyy-MM-dd";
            this.bgDateEdit3.Properties.EditFormat.FormatString = "yyyy-MM-dd";
            this.bgDateEdit3.Properties.Mask.EditMask = "yyyy-MM-dd";

        }

        private void btnMSSQLConfig_Click(object sender, EventArgs e)
        {
            string msSqlConnStr = EditMSSQLConnStr.EditValue.ToString();
            bool isSubTable = ckEditSubTable.Checked;
            string subTableType = cbEditSubTableType.EditValue.ToString();
            DbHelperSQL.Query(@" if exists(select * from " + msSQLDBName + @".dbo.Ba_DBConfig where DBConfigType='mssql') 
                                 begin
                                     update " + msSQLDBName + @".dbo.Ba_DBConfig set ConnStr=@ConnStr,IsSubTable=@IsSubTable,SubTableType=@SubTableType where DBConfigType='mssql'
                                 end
                                 else
                                 begin
                                     insert into " + msSQLDBName + @".dbo.Ba_DBConfig(DBConfigType,ConnStr,IsSubTable,SubTableType) values('mssql',@ConnStr,@IsSubTable,@SubTableType)
                                 end
                               ", new SqlParameter[] {
                                  new SqlParameter("ConnStr",msSqlConnStr),
                                  new SqlParameter("IsSubTable",isSubTable?1:0),
                                  new SqlParameter("SubTableType",subTableType)
            });
            XtraMessageBox.Show("配置成功");
        }

        private void btnSearch1_Click(object sender, EventArgs e)
        {
            pageIndex1 = 1;
            SQLGridPaging();
        }


        private void SQLGridPaging()
        {
            //查询数据表结构
            string sql = " 1=1 ";
            if (bgDateEdit1.EditValue != null && !string.IsNullOrEmpty(bgDateEdit1.EditValue.ToString()))
            {
                sql += " and CreateTime>=''" + bgDateEdit1.EditValue.ToString() + "'' ";
            }
            if (edDateEdit1.EditValue != null && !string.IsNullOrEmpty(edDateEdit1.EditValue.ToString()))
            {
                sql += " and CreateTime<=''" + edDateEdit1.EditValue.ToString() + "'' ";
            }
            if (editEquipName.EditValue != null && !string.IsNullOrEmpty(editEquipName.EditValue.ToString()))
            {
                sql += " and EquipID= " + editEquipName.EditValue;
            }
            if (editVarName.EditValue != null && !string.IsNullOrEmpty(editVarName.EditValue.ToString()))
            {
                sql += " and VarName like ''%" + editVarName.EditValue.ToString() + "%'' ";
            }

            string dbName = "";
            if (!ckEditSubTable.Checked)
            {
                dbName = "CJ_Record";
            }
            else if (cbEditSubTableType.EditValue.ToString() == "按月分表")
            {
                dbName = "CJ_Record_" + DateTime.Parse(bgDateEdit1.EditValue.ToString()).ToString("yyyyMM");
            }



            //分页存储过程
            string pagingSQL = @"DECLARE @return_value int,
		                                @TotalCount int

                                EXEC	@return_value = [dbo].[usp_Paging]
		                                @Tables = N'" + msSQLDBName + @".dbo."+ dbName + @"',
		                                @PrimaryKey = N'RID DESC',
		                                @pageindex = "+ pageIndex1 + @",
		                                @PageSize = 100,
		                                @Fields = N'*',
		                                @Filter = N'" + sql + @"',
		                                @TotalCount = @TotalCount OUTPUT

                                SELECT	@TotalCount as N'@TotalCount'";
            //MessageBox.Show(pagingSQL);
            DataSet ds1 = DbHelperSQL.Query(pagingSQL, EditMSSQLConnStr.EditValue.ToString());
            gridMSSQL.DataSource = ds1.Tables[0];
           
            int totalCount = int.Parse(ds1.Tables[1].Rows[0][0].ToString());
            totalPage1 = int.Parse(Math.Ceiling(totalCount / 100.0).ToString());
            //dataPaging1.DataSource = dt1;
            dataPaging1.TextStringFormat = "当前 "+ pageIndex1 + " 页，总计 " + totalPage1 + " 页";



        }

        private void dataPaging1_ButtonClick(object sender, NavigatorButtonClickEventArgs e)
        {
            if(e.Button.Tag.ToString() == "prev")
            {
                pageIndex1 = pageIndex1 - 1;
            }
            if (e.Button.Tag.ToString() == "next")
            {
                if (pageIndex1 < totalPage1)
                {
                    pageIndex1 = pageIndex1 + 1;
                }
            }
            if (e.Button.Tag.ToString() == "first")
            {
                pageIndex1 = 1;
            }
            if (e.Button.Tag.ToString() == "end")
            {
                pageIndex1 = totalPage1;
            }
            SQLGridPaging();
        }

        private void btnRedisConfig_Click(object sender, EventArgs e)
        {
            string redisConnStr = EditRedisConnStr.EditValue.ToString();
            string dbNum = cboEditDB.SelectedItem.ToString();
            DbHelperSQL.Query(@" if exists(select * from " + msSQLDBName + @".dbo.Ba_DBConfig where DBConfigType='redis') 
                                 begin
                                     update " + msSQLDBName + @".dbo.Ba_DBConfig set ConnStr=@ConnStr,DBNum = @DBNum where DBConfigType='redis'
                                 end
                                 else
                                 begin
                                     insert into " + msSQLDBName + @".dbo.Ba_DBConfig(DBConfigType,ConnStr,DBNum) values('redis',@ConnStr,@DBNum)
                                 end
                               ", new SqlParameter[] {
                                  new SqlParameter("ConnStr",redisConnStr),
                                  new SqlParameter("DBNum",dbNum)
            });
            XtraMessageBox.Show("配置成功");
        }

        private void brnSearch3_Click(object sender, EventArgs e)
        {
            List<string> listConnAddress = new List<string>();
            listConnAddress.Add(EditRedisConnStr.EditValue.ToString());
            RedisHelper redisHelper = new RedisHelper(listConnAddress);
            ////先存一顿数据进去
            //for (int i = 0; i < 400; i++)
            //{
            //    string name = "1_计件数_" + DateTimeOffset.Now.ToString("yyyyMMddHHmmss");
            //    redisHelper.Set(name, "123", null, long.Parse(cboEditDB.SelectedItem.ToString()));
            //}
            string pattern = "";
            if (editEquipName3.EditValue != null && !string.IsNullOrEmpty(editEquipName3.EditValue.ToString()))
            {
                pattern += editEquipName3.EditValue.ToString() + "_";
            }
            else
            {
                pattern += "*_";
            }
            if (editVarName3.EditValue != null && !string.IsNullOrEmpty(editVarName3.EditValue.ToString()))
            {
                pattern += editVarName3.EditValue.ToString() + "_";
            }
            else
            {
                pattern += "*_";
            }
            if (bgDateEdit3.EditValue != null && !string.IsNullOrEmpty(bgDateEdit3.EditValue.ToString()))
            {
                pattern += DateTime.Parse(bgDateEdit3.EditValue.ToString()).ToString("yyyyMMdd");
            }
            else
            {
                pattern += "*";
            }
            Dictionary<string, string> result = redisHelper.GetByPattern(pattern, cboEditDB.SelectedItem.ToLong());
            gridRedis.DataSource = result.Select(x => new
            {
                RedisDeviceNo = x.Key.Split('_')[0], 
                RedisName = x.Key.Split('_')[1],
                RedisValue = x.Value,
                CollectTime = x.Key.Split('_')[2].Substring(0, 4) + "-" + x.Key.Split('_')[2].Substring(4, 2) + "-" + x.Key.Split('_')[2].Substring(6, 2) + " " + x.Key.Split('_')[2].Substring(8, 2) + ":" + x.Key.Split('_')[2].Substring(10, 2) + ":" + x.Key.Split('_')[2].Substring(12)
            });

        }

        private void btnMongoDBConfig_Click(object sender, EventArgs e)
        {
            string mongoDBConnStr = EditMongoDBConnStr.EditValue.ToString();
            DbHelperSQL.Query(@" if exists(select * from " + msSQLDBName + @".dbo.Ba_DBConfig where DBConfigType='mongodb') 
                                 begin
                                     update " + msSQLDBName + @".dbo.Ba_DBConfig set ConnStr=@ConnStr where DBConfigType='mongodb'
                                 end
                                 else
                                 begin
                                     insert into " + msSQLDBName + @".dbo.Ba_DBConfig(DBConfigType,ConnStr) values('mongodb',@ConnStr)
                                 end
                               ", new SqlParameter[] {
                                  new SqlParameter("ConnStr",mongoDBConnStr)
            });
            XtraMessageBox.Show("配置成功");
        }

        private void brnSearch2_Click(object sender, EventArgs e)
        {
            //目前插入一条数据
            string mongoDBConnStr = EditMongoDBConnStr.EditValue.ToString().Split(';')[0];
            string mongoDBDatabase = EditMongoDBConnStr.EditValue.ToString().Split(';')[1];
            //MongoDBHelper.SetConfig(mongoDBConnStr, mongoDBDatabase);
            //MongoDBCJRet cjRet = new MongoDBCJRet();
            //cjRet.Id = new MongoDB.Bson.ObjectId(Guid.NewGuid().ToString("N"));
            //cjRet.DeviceNo = "1";
            //cjRet.CJDateTime = MongoDB.Bson.BsonDateTime.Create(DateTime.Now);
            //cjRet.CJParams = new List<MongoDBCJParam>() {
            //    new MongoDBCJParam(){ KeyName="加工数",KeyValue="10001" },
            //    new MongoDBCJParam(){ KeyName="节拍数",KeyValue="30" }
            //};
            //MongoDBHelper.InsertOne<MongoDBCJRet>("CJ_Record", cjRet);


            //string mongoDBConnStr = EditMongoDBConnStr.EditValue.ToString().Split(';')[0];
            //string mongoDBDatabase = EditMongoDBConnStr.EditValue.ToString().Split(';')[1];




            var filters = new FilterDefinitionBuilder<MongoDBCJRet>();
            FilterDefinition<MongoDBCJRet> filterResult = null;
            if (bgDateEdit2.EditValue != null && !string.IsNullOrEmpty(bgDateEdit2.EditValue.ToString()))
            {
                filterResult = filters.And(Builders<MongoDBCJRet>.Filter.Gte(x => x.CJDateTime, bgDateEdit2.EditValue));
            }
            if (edDateEdit2.EditValue != null && !string.IsNullOrEmpty(edDateEdit2.EditValue.ToString()))
            {
                filterResult = filters.And(Builders<MongoDBCJRet>.Filter.Lte(x => x.CJDateTime, edDateEdit2.EditValue));
            }
            if (editEquipName2.EditValue != null && !string.IsNullOrEmpty(editEquipName2.EditValue.ToString()))
            {
                filterResult = filters.And(Builders<MongoDBCJRet>.Filter.Regex(x => x.DeviceNo,new BsonRegularExpression("/"+ editEquipName2.EditValue.ToString() + "/i")));
            }


            if (filterResult == null)
            {
                filterResult = filters.Empty;
            }

            MongoDBHelper.SetConfig(mongoDBConnStr, mongoDBDatabase);
            var ret = MongoDBHelper.GetAll<MongoDBCJRet>(mongoDBConnStr, mongoDBDatabase, "CJ_Record", filterResult);
            gridMongoDB.DataSource = ret.Select(x => new
            {
                MongoDBDeviceNo = x.DeviceNo,
                MongoDBCJTime = x.CJDateTime.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss"),
                MongoDBParamStr = x.CJParams.ToJson()
            }).ToList();
        }
    }
}
